package com.demo.Controller;

import com.demo.entity.ExcelPo;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.springframework.util.ResourceUtils;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import javax.swing.filechooser.FileSystemView;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @Author zqc
 * @Date 2020/12/8-10:28
 * @Description  https://blog.csdn.net/vbirdbest/article/details/72870714
 */
@Api(tags = "Excel相关操作")
@RestController
@RequestMapping("/office")
public class ExcelTestController {

    /**
     * 导出excel表格
     * @throws IOException
     */
    public static void createExcel() throws IOException {
        //获取桌面路径
        FileSystemView fsv = FileSystemView.getFileSystemView();
        String desktop = fsv.getHomeDirectory().getPath();
        String filePath = desktop+"/template.xls";

        File file = new File(filePath);
        OutputStream outputStream = new FileOutputStream(file);

        //创建一个工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
        //创建一个sheet
        HSSFSheet sheet = workbook.createSheet("Sheet1");
        //创建新行，需要指定行号，行号从0开始
        HSSFRow row = sheet.createRow(0);
//        //创建单元格样式
//        HSSFCellStyle hssfCellStyle = workbook.createCellStyle();
//        //设置字体样式
//        HSSFFont hssfFont = workbook.createFont();
//        hssfFont.setFontName("华文行楷");
//        hssfFont.setFontHeightInPoints((short)22);
//        hssfFont.setColor(HSSFColor.BLACK.index);
//        hssfCellStyle.setFont(hssfFont);
//        row.setRowStyle(hssfCellStyle);
        //创建新的单元格
        row.createCell(0).setCellValue("id");
        row.createCell(1).setCellValue("订单号");
        row.createCell(2).setCellValue("下单时间");
        row.createCell(3).setCellValue("个数");
        row.createCell(4).setCellValue("单价");
        row.createCell(5).setCellValue("订单金额");
        //设置行的高度
        row.setHeightInPoints(30);


        HSSFRow row1 = sheet.createRow(1);
        row1.createCell(0).setCellValue("1");
        row1.createCell(1).setCellValue("NO00001");

        //创建单元格样式
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        HSSFCreationHelper creationHelper = workbook.getCreationHelper();
        //日期格式化
        cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));
        //设置列的宽度
        sheet.setColumnWidth(2,20*256);

        HSSFCell cell2 = row1.createCell(2);
        cell2.setCellStyle(cellStyle);
        cell2.setCellValue(new Date());

        row1.createCell(3).setCellValue(2);

        //保留两位小数
        HSSFCellStyle cellStyle2 = workbook.createCellStyle();
        cellStyle2.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
        HSSFCell cell4 = row1.createCell(4);
        cell4.setCellStyle(cellStyle2);
        cell4.setCellValue(19.5);

        //货币格式化
        HSSFCellStyle cellStyle3 = workbook.createCellStyle();
        //设置字体样式
        HSSFFont font = workbook.createFont();
        font.setFontName("华文行楷");
        font.setFontHeightInPoints((short) 15);
        font.setColor(HSSFColor.RED.index);
        cellStyle3.setFont(font);

        HSSFCell cell5 = row1.createCell(5);
        cell5.setCellStyle(cellStyle3);
        //设置计算公式
        cell5.setCellFormula("D2*E2");

        //获取计算公式的值
        HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(workbook);
        cell5 = e.evaluateInCell(cell5);
        System.out.println("cell5："+cell5.getNumericCellValue());

        //设置默认选中的工作表
        workbook.setActiveSheet(0);

        workbook.write(outputStream);
        outputStream.close();
    }

    /**
     * 读取excel文件内容
     * @throws IOException
     */
    public static void readExcel() throws IOException {
        //获取桌面路径
        FileSystemView fsv = FileSystemView.getFileSystemView();
        String desktop = fsv.getHomeDirectory().getPath();
        String filePath = desktop+"/template.xls";

        FileInputStream fileInputStream = new FileInputStream(filePath);
        BufferedInputStream bufferedInputStream = new BufferedInputStream(fileInputStream);
        POIFSFileSystem fileSystem = new POIFSFileSystem(bufferedInputStream);

        HSSFWorkbook workbook = new HSSFWorkbook(fileSystem);
        HSSFSheet sheet = workbook.getSheet("Sheet1");

        //获取最后的行的索引，没有行或者只有一行的时候返回0
        int lastRowIndex = sheet.getLastRowNum();
        System.out.println("lastRowIndex："+lastRowIndex);
        for(int i=0;i<=lastRowIndex;i++){
            HSSFRow row = sheet.getRow(i);
            if(row == null) break;

            //获取最后的单元格号，如果单元格有第一个开始算，lastCellNum就是列的个数
            short lastCellNum = row.getLastCellNum();
            System.out.println("lastCellNum："+lastCellNum);
            for(int j=0;j<lastCellNum;j++){
                //取数字时需要设置
                row.getCell(j).setCellType(Cell.CELL_TYPE_STRING);
                //注意日期需要格式转换

                String cellValue = row.getCell(j).getStringCellValue();
                System.out.println("cellValue："+cellValue);
            }
        }
        bufferedInputStream.close();
    }


    @ApiOperation("使用模板来操作excel")
    @GetMapping("/excelDownload")
    public static void createExcelByModel(HttpServletResponse response){        //HttpServletResponse response
        try {
            //获取本地文件
            String serverpath = ResourceUtils.getURL("classpath:static").getPath().replace("%20"," ");
            //从路径字符串中取出工程路径
            String path_prefix = serverpath.substring(1);
            String path =  path_prefix+"/excelModel.xls";
            File file = new File(path);
            FileInputStream fileInputStream = new FileInputStream(file);
            InputStream inputstream = fileInputStream;
            // 获取模板
            HSSFWorkbook workbook = new HSSFWorkbook(inputstream);

//            // 获取远端的模板
//            InputStream stream = new ClassPathResource("classpath:BOOT-INF/classes/static/monitor.xls").getInputStream();
//            //创建一个HSSFWorkbook对象，对应一个Excel文件
//            HSSFWorkbook workbook = new HSSFWorkbook(stream);

            // 获取模板sheet页
            HSSFSheet sheet = workbook.getSheetAt(0);

            sheet.setForceFormulaRecalculation(true);

            //创造数据
            List<ExcelPo> userList = new ArrayList<>();
            ExcelPo excelPo = new ExcelPo(Long.valueOf(1),"妖尾","20170390888",Integer.valueOf(88));
            ExcelPo excelPo2 = new ExcelPo(Long.valueOf(2),"魔笛","20170390898",Integer.valueOf(98));
            ExcelPo excelPo3 = new ExcelPo(Long.valueOf(3),"海贼王","20170390878",Integer.valueOf(78));
            ExcelPo excelPo4 = new ExcelPo(Long.valueOf(4),"死神","20170390868",Integer.valueOf(88));
            userList.add(excelPo);
            userList.add(excelPo2);
            userList.add(excelPo3);
            userList.add(excelPo4);

            //数据写入excel
            for(int i=0;i<userList.size();i++){
                HSSFRow row = sheet.createRow(i+2);
                ExcelPo row0 = userList.get(i);
                //列循环
                for(int j=0;j<4;j++){
                    HSSFCell cell = row.createCell(j);
                    //反射
                    Field[] fields = row0.getClass().getDeclaredFields();
                    //j+1跳过实体类前1条数据;反射获取数据名字+类型
                    String name = fields[j].getName();
                    String type = fields[j].getGenericType().toString();
                    //首字母大写
                    name = name.substring(0, 1).toUpperCase() + name.substring(1);
                    Method m = row0.getClass().getMethod("get"+name);

                    if("class java.lang.String".equals(type)) {
                        String value = (String) m.invoke(row0);
                        if (!"".equals(value) && value != null) {
                            cell.setCellValue(value);
                        }else{
                            cell.setCellValue("/");
                        }
                    }else if("class java.lang.Long".equals(type)) {
                        Long value = (Long) m.invoke(row0);
                        if (!"".equals(value) && value != null) {
                            cell.setCellValue(value);
                        }else{
                            cell.setCellValue("/");
                        }
                    }else if("class java.lang.Integer".equals(type)) {
                        Integer value = (Integer) m.invoke(row0);
                        if (!"".equals(value) && value != null) {
                            cell.setCellValue(value);
                        }else{
                            cell.setCellValue("/");
                        }
                    }
                }
            }

            String fileName = "xxoo.xls";
            response.reset();
            response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
            OutputStream os = new BufferedOutputStream(response.getOutputStream());

            workbook.write(os);
            os.flush();
            os.close();

        } catch (Exception e) {
            System.out.println("使用模板导出excel表格有误...");
            e.printStackTrace();
        }

    }


    public static void main(String[] args) {

        try {
            System.out.println("开始导出excel表格...");
            createExcel();
            System.out.println("导出excel表格success...");

            System.out.println("========================");

            System.out.println("开始读取excel表格数据...");
            readExcel();
            System.out.println("读取excel表格success...");
        } catch (IOException e) {
            System.out.println("操作excel表格failure...");
            e.printStackTrace();
        }
    }

}
